(bug?) InnoDB MySQL错误1025，errno 150外键

2023-03-20 23:26| 来源: 网络整理| 查看: 265

百度翻译此文有道翻译此文问题描述

I have a table whose primary key I'm trying to change. this is the table definition.

CREATE TABLE `tbl_customer` ( `PersonId` int(11) NOT NULL, `Id` int(10) unsigned NOT NULL, `Name` varchar(100) collate utf8_spanish_ci NOT NULL, `Alias` varchar(50) collate utf8_spanish_ci NOT NULL, `Phone` varchar(30) collate utf8_spanish_ci default NULL, `Phone2` varchar(30) collate utf8_spanish_ci default NULL, `Email` varchar(50) collate utf8_spanish_ci default NULL, `Email2` varchar(50) collate utf8_spanish_ci default NULL, `RFC` varchar(13) collate utf8_spanish_ci default NULL, `AddressStreetName` varchar(45) collate utf8_spanish_ci default NULL, `AddressStreetNumber` varchar(45) collate utf8_spanish_ci default NULL, `AddressCityWard` varchar(45) collate utf8_spanish_ci default NULL, `AddressCityName` varchar(45) collate utf8_spanish_ci default NULL, `AddressStateName` varchar(45) collate utf8_spanish_ci default NULL, `AddressCountryName` varchar(45) collate utf8_spanish_ci default NULL, `AddressPostalCode` int(10) default NULL, `IsDistributor` tinyint(1) NOT NULL default '0' COMMENT '1 = Is Distributor, 0 = Is Not Distributor', `ParentCustomerId` int(10) NOT NULL default '11' COMMENT 'Our Id is 11, so by default, all customers right now are our children.', PRIMARY KEY (`Id`), KEY `fk_tbl_cliente_tbl_cliente1_idx` (`ParentCustomerId`), KEY `fk_tbl_cliente_tbl_person1_idx` (`PersonId`), KEY `PersonId` (`PersonId`), KEY `PersonId_2` (`PersonId`), CONSTRAINT `fk_tbl_cliente_tbl_cliente1` FOREIGN KEY (`ParentCustomerId`) REFERENCES `tbl_customer` (`PersonId`), CONSTRAINT `fk_tbl_cliente_tbl_person1` FOREIGN KEY (`PersonId`) REFERENCES `zapata`.`tbl_person` (`Id`) ON DELETE NO ACTION ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT='''Customer'' refers to a person or entity to which we provide '$$

Now, when I first tried to:

ALTER TABLE `tbl_customer` DROP PRIMARY KEY;

My PRIMARY KEY is Id . When I tried to drop it I got..

Error Code: 1025. Error on rename of './services/#sql-29a_218cc7f' to './services/tbl_customer' (errno: 150)

So, I deleted all FOREIGN KEY constraints that referred to this table and column, and still got the same error. I also went over to SHOW ENGINE INNODB STATUS And found out this:

------------------------ LATEST FOREIGN KEY ERROR ------------------------ 130226 14:41:11 Error in foreign key constraint of table services/tbl_employee_shift: there is no index in referenced table which would contain the columns as the first columns, or the data types in the referenced table do not match to the ones in table. Constraint: , CONSTRAINT fk_tbl_employee_shift_tbl_customer1 FOREIGN KEY (CustomerId) REFERENCES services.tbl_customer (Id) ON UPDATE CASCADE

However, the table services.tbl_employee_shift does not exist (it existed once but it was dropped several weeks before I tried this change). So I went on and...

CREATE TABLE services.tbl_employee_shift( CustomerId INT (11) ); ALTER TABLE services.tbl_employee_shift ADD CONSTRAINT fk_tbl_employee_shift_tbl_customer1 FOREIGN KEY (CustomerId) REFERENCES avatar.tbl_cliente (Id); ALTER TABLE services.tbl_employee_shift DROP FOREIGN KEY fk_tbl_employee_shift_tbl_customer1;

And it works... but it doesn't correct the necessary information, seemingly InnoDB still believes that the constraint fk_tbl_employee_shift_tbl_customer1 is alive and thus, is 'preventing the drop of the primary key to keep consistency'... I'm using MySQL 5.0.95.

EDIT: This problem went unresolved, it was worked around

The problem could only be corrected when we migrated the database to a newer server (same mysql version), seems like there was a broken/ghost reference to a ghost foreign key (fk_tbl_employee_shift_tbl_customer1 ) which prevented the column from being dropped. Since this broken/ghostfk wasn't in the new server, I could drop the column with no problems then. My guess is it was a bug, but unfortunately I can't recreate it.

推荐答案

It sounds as though you dropped tbl_employee_shift whilst foreign_key_checks was set to 0:

Setting foreign_key_checks to 0 also affects data definition statements: DROP SCHEMA drops a schema even if it contains tables that have foreign keys that are referred to by tables outside the schema, and DROP TABLE drops tables that have foreign keys that are referred to by other tables.

Since this behaviour is documented, it must be considered by-design and therefore not a bug.

其他推荐答案

The error occurs when foreign key is bad formulated. the SQL is correct, i run script in my localhost, i get same error. the solution is verify that table tbl_person was created with engine "InnoDB".

greetings

其他推荐答案

Just dealt with this problem today. There was no sign of the two affected tables in any of the schema information tables. I searched the various system databases looking for the FK, to no avail. In the end, dropping the database worked instantly.

【本文地址】

公司简介

联系我们